export interface WithdrawalCaseRecord {
  id: string;
  fileName: string;
  sheetName: string;
  organization: string;
  name: string;
  idCard: string;
  reason: string;
  phone: string;
  updateDate: string;
  remark: string;
  status: number;
  withdrawalTime?: string;
  clientId?: string;
  channelId?: string;
  createdAt: number;
  updatedAt: number;
}

export interface ExcelFileRecord {
  id: string;
  fileName: string;
  fileSize: number;
  uploadTime: number;
  processedTime: number;
  totalRecords: number;
  todayRecords: number;
}

export class WithdrawalCaseDB {
  private dbName = 'WithdrawalCaseDB';
  private version = 1;
  private db: IDBDatabase | null = null;

  async init(): Promise<void> {
    return new Promise((resolve, reject) => {
      const request = indexedDB.open(this.dbName, this.version);
      
      request.onerror = () => reject(request.error);
      request.onsuccess = () => {
        this.db = request.result;
        resolve();
      };
      
      request.onupgradeneeded = (event) => {
        const db = (event.target as IDBOpenDBRequest).result;
        
        if (!db.objectStoreNames.contains('withdrawalCases')) {
          const caseStore = db.createObjectStore('withdrawalCases', { keyPath: 'id' });
          caseStore.createIndex('fileName', 'fileName', { unique: false });
          caseStore.createIndex('sheetName', 'sheetName', { unique: false });
          caseStore.createIndex('name', 'name', { unique: false });
          caseStore.createIndex('updateDate', 'updateDate', { unique: false });
          caseStore.createIndex('status', 'status', { unique: false });
          caseStore.createIndex('createdAt', 'createdAt', { unique: false });
        }
        
        if (!db.objectStoreNames.contains('excelFiles')) {
          const fileStore = db.createObjectStore('excelFiles', { keyPath: 'id' });
          fileStore.createIndex('fileName', 'fileName', { unique: true });
          fileStore.createIndex('uploadTime', 'uploadTime', { unique: false });
        }
      };
    });
  }

  async upsertWithdrawalCase(record: WithdrawalCaseRecord): Promise<void> {
    if (!this.db) throw new Error('Database not initialized');
    
    return new Promise((resolve, reject) => {
      const transaction = this.db!.transaction(['withdrawalCases'], 'readwrite');
      const store = transaction.objectStore('withdrawalCases');
      
      const getRequest = store.get(record.id);
      getRequest.onsuccess = () => {
        const existingRecord = getRequest.result;
        if (existingRecord) {
          const updatedRecord = {
            ...existingRecord,
            ...record,
            updatedAt: Date.now()
          };
          const putRequest = store.put(updatedRecord);
          putRequest.onsuccess = () => resolve();
          putRequest.onerror = () => reject(putRequest.error);
        } else {
          const newRecord = {
            ...record,
            createdAt: Date.now(),
            updatedAt: Date.now()
          };
          const addRequest = store.add(newRecord);
          addRequest.onsuccess = () => resolve();
          addRequest.onerror = () => reject(addRequest.error);
        }
      };
      getRequest.onerror = () => reject(getRequest.error);
    });
  }

  async upsertWithdrawalCases(records: WithdrawalCaseRecord[]): Promise<void> {
    if (!this.db) throw new Error('Database not initialized');
    
    return new Promise((resolve, reject) => {
      const transaction = this.db!.transaction(['withdrawalCases'], 'readwrite');
      const store = transaction.objectStore('withdrawalCases');
      
      let completed = 0;
      const total = records.length;
      
      if (total === 0) {
        resolve();
        return;
      }
      
      records.forEach(record => {
        const getRequest = store.get(record.id);
        getRequest.onsuccess = () => {
          const existingRecord = getRequest.result;
          if (existingRecord) {
            const updatedRecord = {
              ...existingRecord,
              ...record,
              updatedAt: Date.now()
            };
            store.put(updatedRecord);
          } else {
            const newRecord = {
              ...record,
              createdAt: Date.now(),
              updatedAt: Date.now()
            };
            store.add(newRecord);
          }
          
          completed++;
          if (completed === total) {
            resolve();
          }
        };
        getRequest.onerror = () => {
          reject(getRequest.error);
        };
      });
    });
  }

  async getAllWithdrawalCases(): Promise<WithdrawalCaseRecord[]> {
    if (!this.db) throw new Error('Database not initialized');
    
    return new Promise((resolve, reject) => {
      const transaction = this.db!.transaction(['withdrawalCases'], 'readonly');
      const store = transaction.objectStore('withdrawalCases');
      const request = store.getAll();
      
      request.onsuccess = () => resolve(request.result);
      request.onerror = () => reject(request.error);
    });
  }

  async getWithdrawalCasesByFileName(fileName: string): Promise<WithdrawalCaseRecord[]> {
    if (!this.db) throw new Error('Database not initialized');
    
    return new Promise((resolve, reject) => {
      const transaction = this.db!.transaction(['withdrawalCases'], 'readonly');
      const store = transaction.objectStore('withdrawalCases');
      const index = store.index('fileName');
      const request = index.getAll(fileName);
      
      request.onsuccess = () => resolve(request.result);
      request.onerror = () => reject(request.error);
    });
  }

  async updateWithdrawalCaseStatus(id: string, status: number, withdrawalTime?: string, remark?: string): Promise<void> {
    if (!this.db) throw new Error('Database not initialized');
    
    return new Promise((resolve, reject) => {
      const transaction = this.db!.transaction(['withdrawalCases'], 'readwrite');
      const store = transaction.objectStore('withdrawalCases');
      
      const getRequest = store.get(id);
      getRequest.onsuccess = () => {
        const record = getRequest.result;
        if (record) {
          record.status = status;
          record.updatedAt = Date.now();
          if (withdrawalTime) {
            record.withdrawalTime = withdrawalTime;
          }
          if (remark !== undefined) {
            record.remark = remark;
          }
          
          const putRequest = store.put(record);
          putRequest.onsuccess = () => resolve();
          putRequest.onerror = () => reject(putRequest.error);
        } else {
          reject(new Error('Record not found'));
        }
      };
      getRequest.onerror = () => reject(getRequest.error);
    });
  }

  async getWithdrawalCaseById(id: string): Promise<WithdrawalCaseRecord | null> {
    if (!this.db) throw new Error('Database not initialized');
    
    return new Promise((resolve, reject) => {
      const transaction = this.db!.transaction(['withdrawalCases'], 'readonly');
      const store = transaction.objectStore('withdrawalCases');
      
      const request = store.get(id);
      request.onsuccess = () => resolve(request.result || null);
      request.onerror = () => reject(request.error);
    });
  }

  async deleteWithdrawalCase(id: string): Promise<void> {
    if (!this.db) throw new Error('Database not initialized');
    
    return new Promise((resolve, reject) => {
      const transaction = this.db!.transaction(['withdrawalCases'], 'readwrite');
      const store = transaction.objectStore('withdrawalCases');
      
      const request = store.delete(id);
      request.onsuccess = () => resolve();
      request.onerror = () => reject(request.error);
    });
  }

  async addExcelFile(fileRecord: ExcelFileRecord): Promise<void> {
    if (!this.db) throw new Error('Database not initialized');
    
    return new Promise((resolve, reject) => {
      const transaction = this.db!.transaction(['excelFiles'], 'readwrite');
      const store = transaction.objectStore('excelFiles');
      
      const request = store.put(fileRecord);
      request.onsuccess = () => resolve();
      request.onerror = () => reject(request.error);
    });
  }

  async getAllExcelFiles(): Promise<ExcelFileRecord[]> {
    if (!this.db) throw new Error('Database not initialized');
    
    return new Promise((resolve, reject) => {
      const transaction = this.db!.transaction(['excelFiles'], 'readonly');
      const store = transaction.objectStore('excelFiles');
      const request = store.getAll();
      
      request.onsuccess = () => resolve(request.result);
      request.onerror = () => reject(request.error);
    });
  }

  async clearAllData(): Promise<void> {
    if (!this.db) throw new Error('Database not initialized');
    
    return new Promise((resolve, reject) => {
      const transaction = this.db!.transaction(['withdrawalCases', 'excelFiles'], 'readwrite');
      
      const caseStore = transaction.objectStore('withdrawalCases');
      const fileStore = transaction.objectStore('excelFiles');
      
      const caseRequest = caseStore.clear();
      const fileRequest = fileStore.clear();
      
      let completed = 0;
      const total = 2;
      
      const checkComplete = () => {
        completed++;
        if (completed === total) {
          resolve();
        }
      };
      
      caseRequest.onsuccess = checkComplete;
      caseRequest.onerror = () => reject(caseRequest.error);
      
      fileRequest.onsuccess = checkComplete;
      fileRequest.onerror = () => reject(fileRequest.error);
    });
  }

  async cleanupOldData(daysToKeep: number = 7): Promise<number> {
    if (!this.db) throw new Error('Database not initialized');
    
    const cutoffDate = Date.now() - (daysToKeep * 24 * 60 * 60 * 1000);
    
    return new Promise((resolve, reject) => {
      const transaction = this.db!.transaction(['withdrawalCases'], 'readwrite');
      const store = transaction.objectStore('withdrawalCases');
      const index = store.index('createdAt');
      
      const range = IDBKeyRange.upperBound(cutoffDate);
      const request = index.openCursor(range);
      
      let deletedCount = 0;
      
      request.onsuccess = (event) => {
        const cursor = (event.target as IDBRequest).result;
        if (cursor) {
          cursor.delete();
          deletedCount++;
          cursor.continue();
        } else {
          resolve(deletedCount);
        }
      };
      
      request.onerror = () => reject(request.error);
    });
  }
}
